﻿#region | Copyright © 2556 |

/*
##########################################################################
	Author By : Siriwat Suananpornpanit
	DateTime : 2556/08/30
##########################################################################
*/

#endregion

#region | Imports |

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using DataAccess;
using DataAccess.DBConnections;
using DataAccess.ProxyConnections;
using Oracle.DataAccess.Client;

#endregion

namespace Web.Business
{
    public partial class FunctionApplicationBal
    {
        #region | Conts |

        public static DataAccessClient dal;//= new DataAccessClient(DATABASE_PROXY.AUTH);

        private static DBConnectionsEngine dbEngine;

        private static OracleConnection Conn;

        private static Hashtable hashtable;

        private static FunctionApplicationBal _dal;

        public static object result = null;

        private static System.String sql = "";

        private static String _err = null;

        public static FunctionApplicationBal GetInstance()
        {
            /* for Init */
            dal = new DataAccessClient(DATABASE_PROXY.AUTH);

            dbEngine = new DBConnectionsEngine();

            Conn = new DatabaseProxy().GetAuthConnection();

            _dal = new FunctionApplicationBal();

            return _dal;
        }

        #endregion

        #region | Helper |
        
        internal bool Insert(p_FUNCTION_APPLICATION obj, out string err)
        {
            bool result = false;

            if (dal.Insert(obj, out err) != DB_RESULT.INCOMPLETED)
                result = true;

            return result;
        }

        internal bool Update(p_FUNCTION_APPLICATION sender, out string err)
        {
            bool result = false;

            p_FUNCTION_APPLICATION obj = new p_FUNCTION_APPLICATION();
            obj.APPLNO = sender.APPLNO;
            obj.COMPANY_CODE = sender.COMPANY_CODE;
            obj.FUNCNO = sender.FUNCNO;
            obj.FUNCNAME = sender.FUNCNAME;
            obj.PDAFLAG = sender.PDAFLAG;
            obj.MOBILEURL = sender.MOBILEURL;
            obj.STATUS = sender.STATUS;
            obj.MAINSCREEN = sender.MAINSCREEN;
            obj.REMARK = sender.REMARK;
            obj.PARENTFUNC = sender.PARENTFUNC;
            obj.RUNNING_BY_PARENFUNC = sender.RUNNING_BY_PARENFUNC;

            obj.setCriteria(p_FUNCTION_APPLICATION_FIELD.APPLNO, CRITERIA_FLAG.Equal, sender.APPLNO);
            obj.setCriteria(p_FUNCTION_APPLICATION_FIELD.FUNCNO, CRITERIA_FLAG.Equal, sender.FUNCNO);
            obj.setCriteria(p_FUNCTION_APPLICATION_FIELD.COMPANY_CODE, CRITERIA_FLAG.Equal, sender.COMPANY_CODE);
            obj.setOperator(OPERATOR.AND);

            if (dal.Update(obj, out err) == DB_RESULT.INCOMPLETED)
                result = true;

            return result;
        }

        internal bool Delete(p_FUNCTION_APPLICATION sender, out string err)
        {
            bool result = false;

            p_FUNCTION_APPLICATION obj = new p_FUNCTION_APPLICATION();
            obj.setCriteria(p_FUNCTION_APPLICATION_FIELD.COMPANY_CODE, CRITERIA_FLAG.Equal, sender.COMPANY_CODE);
            obj.setCriteria(p_FUNCTION_APPLICATION_FIELD.APPLNO, CRITERIA_FLAG.Equal, sender.APPLNO);
            obj.setCriteria(p_FUNCTION_APPLICATION_FIELD.FUNCNO, CRITERIA_FLAG.Equal, sender.FUNCNO);
            obj.setOperator(OPERATOR.AND);

            if (dal.Delete(obj, out err) == DB_RESULT.INCOMPLETED)
                result = true;

            return result;
        }

        #endregion

        #region | dbEngine |

        internal String GetFuncApplGenerator(
            System.Web.UI.Page page,
            string companyCode, 
            string applNo = null, 
            string parentNo = null)
        {
            String iGen = null;
            try
            {
                string concast = null;
                if (string.IsNullOrEmpty(parentNo))
                { 
                    sql = string.Format(@" 
                                        SELECT MAX(CAST(LTRIM(substr(FUNCNO,1,3),'F')AS INT)) 
                                        FROM FUNCTION_APPLICATION");

                    concast = @" WHERE 1=1
                                AND LENGTH(FUNCNO)=3 
                                AND PARENTFUNC IS NULL ";

                    if (!String.IsNullOrEmpty(companyCode))
                        concast += string.Format(@"AND COMPANY_CODE = '{0}'", companyCode);

                    if (!String.IsNullOrEmpty(applNo))
                        concast += string.Format(@"AND APPLNO='{0}'", applNo);
                }
                else
                {
                    sql = string.Format(@" 
                                        SELECT MAX(CAST(LTRIM(substr(FUNCNO,1,5),'{0}')AS INT)) 
                                        FROM FUNCTION_APPLICATION ", parentNo);

                    concast = @" WHERE 1=1";

                    if (!String.IsNullOrEmpty(companyCode))
                        concast += string.Format(@"AND COMPANY_CODE = '{0}'", companyCode);

                    if (!String.IsNullOrEmpty(applNo))
                        concast += string.Format(@" AND APPLNO='{0}'", applNo);

                    if (!String.IsNullOrEmpty(applNo))
                        concast += string.Format(@"AND PARENTFUNC='{0}'", parentNo);
                }

                sql = sql + concast;

                object o = dbEngine.ExecuteScalar(sql, Conn);

                Int32 i;

                if(o == DBNull.Value) i = 0;

                else i = Convert.ToInt32(o);

                // LPAD 2 digit
                string e = (String)dbEngine.ExecuteScalar(
                    string.Format("select LPAD('{0}', 2, '0') from dual",
                    i+ 1), Conn);

                iGen = (String.IsNullOrEmpty(parentNo) ? "F" : (parentNo)) + (e);
            }
            catch (System.Exception ex)
            {
                Core.Modal.Exception(
                    page: page,
                    message: Core.Exception.ModalException(
                                        method: System.Reflection.MethodBase.GetCurrentMethod(),
                                        ex: ex));
                iGen = null;
            }
            return iGen;
        }

        internal IEnumerable<T> GetFuncAnonymousT<T>(
            System.Web.UI.Page page,
            T type,
            string companyCode,
            string applNo = null)
        {
            try
            {
                #region | Query Statement |

                sql = string.Format(@" SELECT FA.FUNCNO,FA.FUNCNAME
                                        FROM FUNCTION_APPLICATION FA
                                        ,APPLICATION A
                                        ");

                string concast = @" WHERE 1=1 
                                        AND FA.PARENTFUNC IS NULL
                                        AND FA.APPLNO = A.APPLNO 
                                        AND FA.COMPANY_CODE = A.COMPANY_CODE 
                                        AND A.STATUS='Y'
                                        AND LENGTH(FA.FUNCNO)=3";

                if (!String.IsNullOrEmpty(companyCode))
                    concast += string.Format(@"
                                AND FA.COMPANY_CODE = '{0}'
                                AND A.COMPANY_CODE = '{0}'
                    ", companyCode);

                if (!String.IsNullOrEmpty(applNo))
                    concast += string.Format(@"
                                AND FA.APPLNO = '{0}' 
                                AND A.APPLNO = '{0}'
                    ", applNo);

                concast += string.Format(@"
                                        GROUP BY FA.FUNCNO,FA.FUNCNAME
                                        ORDER BY CAST(LTRIM(FA.FUNCNO,'F') AS INT) ASC");

                sql = sql + concast;

                #endregion
                
                System.Data.DataTable dt = dbEngine.GetDataTable(
                                                                pSql: sql, 
                                                                pConn: Conn);

                if (dt != null)
                    if(dt.Rows.Count > 0)
                        {
                           IQueryable query = (from o in dt.AsEnumerable()
                                                     select new
                                                         {
                                                             funcNo = o.Field<String>("FUNCNO")
                                                             ,
                                                             funcName = o.Field<String>("FUNCNNAME")
                                                         }).ToList().AsQueryable();

                           return query as IEnumerable<T>;
                        }
            }
            catch (System.Exception ex)
            {
                Core.Modal.Exception(
                    page: page,
                    message: Core.Exception.ModalException(
                                        method: System.Reflection.MethodBase.GetCurrentMethod(),
                                        ex: ex));
            }
            return null;
        }

        public IEnumerable<DataAccess.Prop.FunctionApplication> Select(System.Web.UI.Page page, 
            string companyCode, 
            string applNo = null, 
            string applName = null, 
            string funcNo = null, 
            string funcName = null, 
            string optionMobile = null)
        {
            IEnumerable<DataAccess.Prop.FunctionApplication> entities = new List<DataAccess.Prop.FunctionApplication>();
            try
            {
                sql = string.Format(@"SELECT 
                                        FA.APPLNO
                                        ,CONCAT(CONCAT(FA.APPLNO,' : '),A.APPLNAME)  AS APPLNAME
                                        ,FA.FUNCNO
                                        ,CONCAT(CONCAT(FA.FUNCNO,' : '),FA.FUNCNAME)  AS FUNCNAME
                                        ,FA.PARENTFUNC,FA.MAINSCREEN
                                        ,FA.RUNNING_BY_PARENFUNC,FA.PDAFLAG,FA.SEQMENU,FA.SEQSUBMENU1
                                        ,FA.SEQSUBMENU2,FA.MOBILEURL,FA.STATUS,FA.REMARK,FA.CRDATE
                                        ,FA.CRBY,FA.UPDDATE,FA.UPDBY,FA.COMPANY_CODE
                                        FROM FUNCTION_APPLICATION FA, APPLICATION A
                                    ");

                string concast = @" WHERE 1=1              
                                    AND FA.APPLNO = A.APPLNO 
                                    AND FA.COMPANY_CODE = A.COMPANY_CODE 
                                    AND A.STATUS='Y'
                                    ";

                if (!String.IsNullOrEmpty(companyCode))
                    concast += string.Format(@"
                                AND FA.COMPANY_CODE = '{0}'
                                AND A.COMPANY_CODE = '{0}'
                    ", companyCode);

                if (!String.IsNullOrEmpty(applName))
                    concast += string.Format(@"
                                AND (FA.APPLNO LIKE '%{0}%' 
                                    OR A.APPLNO LIKE '%{0}%' 
                                    OR A.APPLNAME LIKE '%{0}%')
                    ", applName);

                if (!String.IsNullOrEmpty(applNo))
                    concast += string.Format(@"
                                AND FA.APPLNO = '{0}' 
                                AND A.APPLNO = '{0}'
                    ", applNo);

                if (!String.IsNullOrEmpty(funcNo))
                    concast += string.Format(@"
                                AND FA.FUNCNO = '{0}'     
                    ", funcNo);

                if (!String.IsNullOrEmpty(funcName))
                    concast += string.Format(@"
                                AND (FA.FUNCNO LIKE '%{0}%' 
                                    OR FA.FUNCNAME LIKE '%{0}%')        
                    ", funcName);

                if (!String.IsNullOrEmpty(optionMobile)){
                    if (optionMobile == "Y")
                    {
                        concast += string.Format(@"
                            AND FA.PDAFLAG='{0}'
                            AND A.PDAFLAG='{0}'
                            ", optionMobile);
                    }
                    else
                    {
                        concast += string.Format(@"
                            AND (
                                (FA.PDAFLAG='{0}' OR FA.PDAFLAG IS NULL)
                                AND
                                (A.PDAFLAG='{0}' OR A.PDAFLAG IS NULL)
                            )
                            ", optionMobile);
                    }
                }

                sql = sql + concast;

                entities = dbEngine.ToList<DataAccess.Prop.FunctionApplication>(sql, Conn).AsParallel();
            }
            catch (System.Exception ex)
            {
                Core.Modal.Exception(
                    page: page,
                    message: Core.Exception.ModalException(
                                        method: System.Reflection.MethodBase.GetCurrentMethod(),
                                        ex: ex));
                entities = null;
            }
            return entities;
        }


        #endregion

    }
}